package com.sun.myproject.daoimpl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.sun.myproject.bean.Book;
import com.sun.myproject.bean.PageBean;
import com.sun.myproject.bean.User;
import com.sun.myproject.dao.UserDao;
import com.sun.myproject.tool.JDBCUtil;
import com.sun.myproject.tool.TextUtils;

public class UserDaoImpl implements UserDao {

	public List<User> findAll() throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		return runner.query("select * from users", new BeanListHandler<User>(User.class));
	}

	public void insert(User user) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		runner.update(
				"insert into users ( `username`, `passwords`, `birthday`, `sex`, `usertype`, `isfull`, `money`)  values(?,?,?,?,?,?,?)",
				user.getUsername(), user.getPasswords(), user.getBirthday(), user.getSex(), user.getusertype(),
				user.getIsfull(), user.getMoney());
	}

	public List<User> searchUser(String username, String usertype) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		String sql = "select * from users where 1=1 ";
		List<String> list = new ArrayList<String>();
		if (!TextUtils.isEmpty(username)) {
			sql = sql + "  and username like ?";
			list.add("%" + username + "%");
		}
		System.out.println(username);
		if (!TextUtils.isEmpty(usertype)) {
			sql = sql + " and usertype = ?";
			list.add(String.valueOf(usertype));
		}
		System.out.println(usertype);
		System.out.println(sql);

		return runner.query(sql, new BeanListHandler<User>(User.class), list.toArray());
	}

	public void delete(int userid) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		runner.update("delete from users where userid = ?", userid);
		System.out.println("delete from users where userid = " + userid);
	}

	public void update(User user) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		runner.update(
				"update users set username = ?,passwords = ?,birthday = ? , sex = ? , usertype = ? , isfull = ?,money = ? where userid = ? ",
				user.getUsername(), user.getPasswords(), user.getBirthday(), user.getSex(), user.getusertype(),
				user.getIsfull(), user.getMoney(), user.getUserid());
	}

	public User findUserById(int userid) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		return runner.query("select * from users where userid = ?", new BeanHandler<User>(User.class), userid);
	}

	public int findCount(String userid) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		if(!"".equals(userid) && null != userid) {
			int id=Integer.parseInt(userid);
			Long result = (Long) runner.query("SELECT COUNT(*) FROM users where userid=?", new ScalarHandler(),id);
			return result.intValue();
		}
		Long result = (Long) runner.query("SELECT COUNT(*) FROM users", new ScalarHandler());
		return result.intValue();
	}

	public List<User> findUserByPage(String userid, int currentPage) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		if (!"".equals(userid) && null != userid) {
			int id=Integer.parseInt(userid);
			return runner.query("select * from users where userid=? limit ? offset ?",
					new BeanListHandler<User>(User.class),id, PAGE_SIZE, (currentPage - 1) * PAGE_SIZE);
		}
		return runner.query("select * from users limit ? offset ?", new BeanListHandler<User>(User.class), PAGE_SIZE,
				(currentPage - 1) * PAGE_SIZE);
	}

	public User searchUserForLogin(String username, String password, String usertype) throws SQLException {
		System.out.println(username + "---" + password + "-----" + usertype);
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		User user = runner.query("select * from users where username=? and passwords=? and usertype = ?",
				new BeanHandler<User>(User.class), username, password, usertype);
		System.out.println(user);
		return runner.query("select * from users where username=? and passwords=? and usertype = ?",
				new BeanHandler<User>(User.class), username, password, usertype);
	}

	public PageBean findUserByPages(String userid, int currentPage) throws SQLException {
		// 封装分页的该页数据
		PageBean<User> pageBean = new PageBean<User>();

		int pageSize = UserDao.PAGE_SIZE;
		pageBean.setCurrentPage(currentPage); // 设置当前页
		pageBean.setPageSize(pageSize); // 设置每页显示多少记录

		UserDao dao = new UserDaoImpl();
		List<User> list = dao.findUserByPage(userid, currentPage);
		pageBean.setList(list); // 设置这一页的学生数据

		// 总的记录数， 总的页数。
		int count = dao.findCount(userid);
		pageBean.setTotalSize(count); // 设置总的记录数
		// 200 ， 10 ==20 201 ， 10 = 21 201 % 10 == 0 ?201 / 10 :201 % 10 + 1
		pageBean.setTotalPage(count % pageSize == 0 ? count / pageSize : (count / pageSize) + 1); // 总页数
		return pageBean;
	}
	@Override
	public List<User> exportExcel(String userid) throws SQLException {
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());

		if (!"".equals(userid) && null != userid) {
			int id=Integer.parseInt(userid);
			String s = "%" + id + "%";
			System.out.println("-----%%%%%--====----" + s);
			return runner.query("select * from users where userid LIKE ?", new BeanListHandler<User>(User.class), s);
		} else {
			System.out.println("-----%%%%%--====--000000--");
			return findAll();
		}
	}

}
